package com.sc.web.gwt.server.persistence;

import com.sc.web.gwt.shared.model.AgentAdmin;
import com.sc.web.gwt.shared.model.UserAccount;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserAccountPersistence extends AbstractDBConnection {
    private AuthenticatorPersistence authenticatorPersistence = new AuthenticatorPersistence();
    private AgentAdminPersistence agentAdminPersistence = new AgentAdminPersistence();
    final int _valid = 1;
    final int _suspended = 2;
    final int _inactivated = 3;

    public List<UserAccount> getAllUserAccounts() {
        String getAllUsersAccounts = "SELECT UserAccountId, UserAccount, authenticators.AuthenticatorId,  \n" +
                "                agentadmins.AgentAdminId, states.stateId, FirstName, LastName, \n" +
                "                identityNumber, DoB, Email, \n" +
                "                Address, City, Nation, \n" +
                "                agentadmins.SiteName, states.State, DES_DECRYPT(authenticators.Seed), useraccounts.AccountCode \n" +
                "                FROM useraccounts INNER JOIN authenticators ON useraccounts.AuthenticatorId = authenticators.AuthenticatorId \n" +
                "                INNER JOIN agentadmins ON UserAccounts.AgentAdminId = AgentAdmins.AgentAdminId \n" +
                "                INNER JOIN states ON useraccounts.StateId = states.StateId";

        List<UserAccount> userAccountList = new ArrayList<UserAccount>();

        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(getAllUsersAccounts);
            while (resultSet.next()) {
                userAccountList.add(new UserAccount(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12),
                        resultSet.getString(13),
                        resultSet.getString(14),
                        resultSet.getString(15),
                        resultSet.getString(16),
                        resultSet.getString(17)));
            }
            // clean
            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + getAllUsersAccounts);
            e.printStackTrace();
        }
        return userAccountList;
    }

    public List<UserAccount> getAllUserAccounts(int agentId) {
        String getAllUsersAccounts = "SELECT UserAccountId, UserAccount, \n" +
                "                agentadmins.AgentAdminId, states.stateId, FirstName, LastName, \n" +
                "                identityNumber, DoB, Email, \n" +
                "                Address, City, Nation, \n" +
                "                agentadmins.SiteName, states.State, useraccounts.AccountCode \n" +
                "                FROM useraccounts \n" +
                "                INNER JOIN agentadmins ON UserAccounts.AgentAdminId = AgentAdmins.AgentAdminId \n" +
                "                INNER JOIN states ON useraccounts.StateId = states.StateId \n" +
                "                WHERE UserAccounts.AgentAdminId = ?";

        List<UserAccount> userAccountList = new ArrayList<UserAccount>();

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(getAllUsersAccounts);
            ps.setInt(1, agentId);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                UserAccount userAccount = new UserAccount();
                userAccount.setUserAccountId(resultSet.getInt(1));
                userAccount.setUserAccount(resultSet.getString(2));
                userAccount.setAgentAdminId(resultSet.getInt(3));
                userAccount.setStateId(resultSet.getInt(4));
                userAccount.setFirstName(resultSet.getString(5));
                userAccount.setLastName(resultSet.getString(6));
                userAccount.setIdentityNumber(resultSet.getString(7));
                userAccount.setDob(resultSet.getString(8));
                userAccount.setEmail(resultSet.getString(9));
                userAccount.setAddress(resultSet.getString(10));
                userAccount.setCity(resultSet.getString(11));
                userAccount.setNation(resultSet.getString(12));
                userAccount.setSiteName(resultSet.getString(13));
                userAccount.setState(resultSet.getString(14));
                if (resultSet.getString(15) != null) {
                    userAccount.setAccountCode(resultSet.getString(15));
                }
                userAccountList.add(userAccount);
            }
            // clean
            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + getAllUsersAccounts);
            e.printStackTrace();
        }
        return userAccountList;
    }

    public List<UserAccount> searchUserAccounts(String searchText, int searchIndex, int agentId) {
        String searchFor;
        switch (searchIndex) {
            case 0:
                searchFor = "UserAccountId";
                break;
            case 1:
                searchFor = "FirstName";
                break;
            case 2:
                searchFor = "LastName";
                break;
            case 3:
                searchFor = "UserAccount";
                break;
            case 4:
                searchFor = "useraccounts.AccountCode";
                break;
            case 5:
                searchFor = "states.State";
                break;
            case 6:
                searchFor = "agentadmins.SiteName";
                break;
            case 7:
                searchFor = "identityNumber";
                break;
            case 8:
                searchFor = "Email";
                break;
            case 9:
                searchFor = "Address";
                break;
            case 10:
                searchFor = "City";
                break;
            case 11:
                searchFor = "Nation";
                break;
            default:
                searchFor = "UserAccountId";
                break;
        }
        String searchUserAccount = "SELECT UserAccountId, UserAccount,  \n" +
                "                agentadmins.AgentAdminId, states.stateId, FirstName, LastName, \n" +
                "                identityNumber, DoB, Email, \n" +
                "                Address, City, Nation, \n" +
                "                agentadmins.SiteName, states.State, useraccounts.AccountCode \n" +
                "                FROM useraccounts \n" +
                "                INNER JOIN agentadmins ON UserAccounts.AgentAdminId = AgentAdmins.AgentAdminId \n" +
                "                INNER JOIN states ON useraccounts.StateId = states.StateId\n" +
                "                WHERE " + searchFor + " REGEXP ? AND UserAccounts.AgentAdminId = ?";

        List<UserAccount> userAccountList = new ArrayList<UserAccount>();

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(searchUserAccount);
            ps.setString(1, searchText);
            ps.setInt(2, agentId);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                UserAccount userAccount = new UserAccount();
                userAccount.setUserAccountId(resultSet.getInt(1));
                userAccount.setUserAccount(resultSet.getString(2));
                userAccount.setAgentAdminId(resultSet.getInt(3));
                userAccount.setStateId(resultSet.getInt(4));
                userAccount.setFirstName(resultSet.getString(5));
                userAccount.setLastName(resultSet.getString(6));
                userAccount.setIdentityNumber(resultSet.getString(7));
                userAccount.setDob(resultSet.getString(8));
                userAccount.setEmail(resultSet.getString(9));
                userAccount.setAddress(resultSet.getString(10));
                userAccount.setCity(resultSet.getString(11));
                userAccount.setNation(resultSet.getString(12));
                userAccount.setSiteName(resultSet.getString(13));
                userAccount.setState(resultSet.getString(14));
                userAccount.setAccountCode(resultSet.getString(15));

                userAccountList.add(userAccount);
            }
            // clean
            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + searchUserAccount);
            e.printStackTrace();
        }
        return userAccountList;
    }

    public boolean suspendUserAccount(int userAccountId) {
        String suspendUserQuery = "UPDATE useraccounts SET StateId = ? WHERE UserAccountId = ?";
        UserAccount userAccount = getUserAccountData(userAccountId);

        if (userAccount.getState().equals("inactivated")) {
            return false;
        }

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(suspendUserQuery);
            ps.setInt(1, (userAccount.getState().equals("Valid")) ? _suspended : _valid);
            ps.setInt(2, userAccountId);
            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + suspendUserQuery);
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public UserAccount getUserAccountData(int userAccountId) {
        String getUserAccountQuery = "SELECT UserAccountId, UserAccount, authenticators.AuthenticatorId, \n" +
                "agentadmins.AgentAdminId, states.stateid, FirstName, LastName,\n" +
                "identityNumber, DoB, Email,\n" +
                "Address, City, Nation,\n" +
                "agentadmins.SiteName, states.State, DES_DECRYPT(authenticators.Seed), useraccounts.AccountCode\n" +
                "FROM useraccounts INNER JOIN authenticators ON useraccounts.AuthenticatorId = authenticators.AuthenticatorId\n" +
                "INNER JOIN agentadmins ON UserAccounts.AgentAdminId = AgentAdmins.AgentAdminId\n" +
                "INNER JOIN states ON useraccounts.StateId = states.StateId\n" +
                "WHERE UserAccountId = ?";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(getUserAccountQuery);
            ps.setInt(1, userAccountId);
            ResultSet resultSet = ps.executeQuery();
            resultSet.first();
            UserAccount userAccount = new UserAccount(
                    resultSet.getInt(1),
                    resultSet.getString(2),
                    resultSet.getInt(3),
                    resultSet.getInt(4),
                    resultSet.getInt(5),
                    resultSet.getString(6),
                    resultSet.getString(7),
                    resultSet.getString(8),
                    resultSet.getString(9),
                    resultSet.getString(10),
                    resultSet.getString(11),
                    resultSet.getString(12),
                    resultSet.getString(13),
                    resultSet.getString(14),
                    resultSet.getString(15),
                    resultSet.getString(16),
                    resultSet.getString(17));
            // clean
            resultSet.close();
            connection.close();
            return userAccount;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + getUserAccountQuery);
            e.printStackTrace();
        }
        return null;
    }

    public boolean insertUserAccount(UserAccount userAccount) {
        String insertUserAccountQuery = "INSERT INTO useraccounts \n" +
                "(useraccount, AuthenticatorId , AgentAdminId, stateId, \n" +
                "FirstName, LastName, IdentityNumber, \n" +
                "DoB, Email, Address, City, Nation, AccountCode) \n" +
                "VALUES (?,(SELECT authenticators.AuthenticatorId FROM authenticators WHERE authenticators.Seed = DES_ENCRYPT(?)), \n" +
                "(SELECT agentadmins.AgentAdminId FROM agentadmins WHERE agentadmins.SiteName = ?), \n" +
                "(SELECT states.stateId FROM States WHERE states.State = ?), \n" +
                "?,?,?,?,?,?,?,?,?)";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(insertUserAccountQuery);
            ps.setString(1, userAccount.getUserAccount());
            ps.setString(2, userAccount.getSeed());
            ps.setString(3, userAccount.getSiteName());
            ps.setString(4, userAccount.getState());
            ps.setString(5, userAccount.getFirstName());
            ps.setString(6, userAccount.getLastName());
            ps.setString(7, userAccount.getIdentityNumber());
            ps.setString(8, userAccount.getDob());
            ps.setString(9, userAccount.getEmail());
            ps.setString(10, userAccount.getAddress());
            ps.setString(11, userAccount.getCity());
            ps.setString(12, userAccount.getNation());
            ps.setString(13, userAccount.getAccountCode());

            ps.execute();
            connection.close();
            return true;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + insertUserAccountQuery);
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateUserAccount(UserAccount userAccount) {
        String updateUserAccountQuery = "UPDATE useraccounts\n" +
                "SET useraccount = ?, \n" +
                "authenticatorId = (SELECT authenticators.authenticatorId FROM authenticators WHERE authenticators.seed = DES_ENCRYPT(?)),\n" +
                "AgentAdminId = (SELECT agentadmins.AgentAdminId FROM agentadmins WHERE agentadmins.SiteName = ?),\n" +
                "stateId = (SELECT states.stateId FROM States WHERE states.State = ?),\n" +
                "FirstName = ?, LastName = ?, IdentityNumber = ?,\n" +
                "DoB = ?, Email = ?, Address = ?, City = ?, Nation = ?, AccountCode = ?\n" +
                "WHERE UserAccountId = ?";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(updateUserAccountQuery);
            ps.setString(1, userAccount.getUserAccount());
            ps.setString(2, userAccount.getSeed());
            ps.setString(3, userAccount.getSiteName());
            ps.setString(4, userAccount.getState());
            ps.setString(5, userAccount.getFirstName());
            ps.setString(6, userAccount.getLastName());
            ps.setString(7, userAccount.getIdentityNumber());
            ps.setString(8, userAccount.getDob());
            ps.setString(9, userAccount.getEmail());
            ps.setString(10, userAccount.getAddress());
            ps.setString(11, userAccount.getCity());
            ps.setString(12, userAccount.getNation());
            ps.setString(13, userAccount.getAccountCode());
            ps.setInt(14, userAccount.getUserAccountId());
            ps.execute();
            connection.close();
            return true;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + updateUserAccountQuery);
            e.printStackTrace();
            return false;
        }
    }

    public UserAccount getUserAccountDataByAccountName(String userAccountName) {
        String getUserAccountQuery = "SELECT UserAccountId, UserAccount, authenticators.AuthenticatorId, \n" +
                "agentadmins.AgentAdminId, states.stateid, FirstName, LastName,\n" +
                "identityNumber, DoB, Email,\n" +
                "Address, City, Nation,\n" +
                "agentadmins.SiteName, states.State, DES_DECRYPT(authenticators.Seed), AccountCode\n" +
                "FROM useraccounts INNER JOIN authenticators ON useraccounts.AuthenticatorId = authenticators.AuthenticatorId\n" +
                "INNER JOIN agentadmins ON UserAccounts.AgentAdminId = AgentAdmins.AgentAdminId\n" +
                "INNER JOIN states ON useraccounts.StateId = states.StateId\n" +
                "WHERE UserAccount = ?";

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(getUserAccountQuery);
            ps.setString(1, userAccountName);
            ResultSet resultSet = ps.executeQuery();
            resultSet.first();
            UserAccount userAccount = new UserAccount(
                    resultSet.getInt(1),
                    resultSet.getString(2),
                    resultSet.getInt(3),
                    resultSet.getInt(4),
                    resultSet.getInt(5),
                    resultSet.getString(6),
                    resultSet.getString(7),
                    resultSet.getString(8),
                    resultSet.getString(9),
                    resultSet.getString(10),
                    resultSet.getString(11),
                    resultSet.getString(12),
                    resultSet.getString(13),
                    resultSet.getString(14),
                    resultSet.getString(15),
                    resultSet.getString(16),
                    resultSet.getString(17));

            AgentAdmin agentAdmin = agentAdminPersistence.getAgentAdmin(userAccount.getSiteId());
            if (!userAccount.getState().equals("Valid") || !agentAdmin.getState().equals("Valid")) return null;

            // clean
            resultSet.close();
            connection.close();
            return userAccount;
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + getUserAccountQuery);
            e.printStackTrace();
        }
        return null;
    }

    public List<UserAccount> getUserAccountDataByFirstAndLastName(String firstName, String lastName) {
        String query = "SELECT UserAccountId, UserAccount, authenticators.AuthenticatorId, \n" +
                "agentadmins.AgentAdminId, states.state, FirstName, LastName,\n" +
                "identityNumber, DoB, Email,\n" +
                "Address, City, Nation,\n" +
                "agentadmins.SiteName, states.State, DES_DECRYPT(authenticators.Seed), AccountCode\n" +
                "FROM useraccounts INNER JOIN authenticators ON useraccounts.AuthenticatorId = authenticators.AuthenticatorId\n" +
                "INNER JOIN agentadmins ON UserAccounts.AgentAdminId = AgentAdmins.AgentAdminId\n" +
                "INNER JOIN states ON useraccounts.StateId = states.StateId" +
                "WHERE UserAccounts.FirstName = ? && UserAccounts.LastName = ?";

        List<UserAccount> userAccountList = new ArrayList<UserAccount>();

        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, firstName);
            ps.setString(2, lastName);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                userAccountList.add(new UserAccount(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getString(9),
                        resultSet.getString(10),
                        resultSet.getString(11),
                        resultSet.getString(12),
                        resultSet.getString(13),
                        resultSet.getString(14),
                        resultSet.getString(15),
                        resultSet.getString(16),
                        resultSet.getString(17)));
            }
            // clean
            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }
        return userAccountList;
    }

    public List<String> getAllAccountCode() {
        String query = "SELECT AccountCode FROM useraccounts";

        List<String> initCodeList = new ArrayList<String>();

        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                if (resultSet.getString(1) != null) {
                    initCodeList.add(resultSet.getString(1));
                }
            }
            // clean
            resultSet.close();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }

        return initCodeList;
    }

    public boolean activateAccount(String accountCode, int authenticatorId) {
//        Authenticator authenticator = authenticatorPersistence.getAuthenticatorByAccountCode(accountCode);
//        if (authenticator == null) return false;

        String query = "UPDATE useraccounts           \n" +
                "SET StateId = ?, accountCode = ?, useraccounts.authenticatorId = ?\n" +
                "WHERE accountCode =?";
        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, "1");
            ps.setString(2, null);
            ps.setInt(3, authenticatorId);
            ps.setString(4, accountCode);
            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
            return false;
        }
        return true;
    }
}
